package net.xju3_4.dao;

import net.xju3_4.common.utils.JdbcUtilsV1;
import net.xju3_4.common.utils.PageInfo;
import net.xju3_4.entity.User;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class UserDao {
    public List<User> list() {
        // 操作数据库
        // 获取连接
        Connection connection = JdbcUtilsV1.getConnection();
        String sql = "select * from user";
        List<User> userList = JdbcUtilsV1.executeQuery(connection, User.class, sql);
        return userList;
    }

    public User login(String username, String password) {
        String sql = "select id,role,username,email,gender,avatar,create_time createTime,update_time updateTime,deleted from user where deleted = 0 and username=? and password=?";
        return JdbcUtilsV1.executeQueryOne(JdbcUtilsV1.getConnection(), User.class, sql, username, password);
    }

    public void insert(User user) {
        String sql = "INSERT INTO user(role,username,password,email,gender,avatar,create_time,update_time) VALUES(?,?,?,?,?,?,?,?)";
        JdbcUtilsV1.executeUpdate(sql, user.getRole(),user.getUsername(),user.getPassword(),user.getEmail(),user.getGender(),user.getAvatar(),user.getCreateTime(),user.getUpdateTime());
    }

    public User selectOne(int id) {
        String sql = "select id,role,username,email,gender,avatar,create_time createTime,update_time updateTime,deleted from user where deleted = 0 and id=?";
        return JdbcUtilsV1.executeQueryOne(JdbcUtilsV1.getConnection(), User.class, sql, id);
    }

    public void deleteById(int id) {
        // 逻辑删除，实际执行update
        String sql = "update user set deleted = 1 where id = ?";
        JdbcUtilsV1.executeUpdate(sql, id);
    }

    public void update(User user) {
        StringBuilder sql = new StringBuilder("UPDATE user SET ");
        List<Object> params = new ArrayList<>();

        // 动态拼接非空字段
        if (user.getUsername() != null &&!user.getUsername().isEmpty()) {
            sql.append( "username = ?, ");
            params.add(user.getUsername());
        }

        if (user.getPassword() != null &&!user.getPassword().isEmpty()) {
            sql.append( "password = ?, ");
            params.add(user.getPassword());
        }
        if (user.getEmail() != null&&!user.getEmail().isEmpty()) {
            sql.append( "email = ?, ");
            params.add(user.getEmail());
        }
        if (user.getGender() != null&&!user.getGender().isEmpty()) {
            sql.append( "gender = ?, ");
            params.add(user.getGender());
        }
        if (user.getAvatar() != null&&!user.getAvatar().isEmpty()) {
            sql.append( "avatar = ?, ");
            params.add(user.getAvatar());
        }
        if (Objects.nonNull(user.getRole())) {
            sql.append( "role = ?, ");
            params.add(user.getRole());
        }
        // 移除最后的逗号和空格
        if (!params.isEmpty()) {
            sql.delete(sql.length() - 2, sql.length());
        }
        // 添加WHERE条件
        sql.append(" WHERE id = ?");
        params.add(user.getId());
        JdbcUtilsV1.executeUpdate(sql.toString(),params.toArray());
    }

    public PageInfo<User> pageList(String username, String gender, Integer pageNo, Integer pageSize) {
        String sql = "select id,role,username,email,gender,avatar,create_time createTime,update_time updateTime,deleted from user where deleted = 0 ";
        List<Object> params = new ArrayList<Object>();
        // 拼接sql 条件查询
        if (username != null && !username.isEmpty()) {
            sql += " and username like ?";
            params.add("%" + username + "%");
        }
        if (gender != null && !gender.isEmpty()) {
            sql += " and gender = ?";
            params.add(gender);
        }
        sql += " limit ?,?";
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);
        List<User> users = JdbcUtilsV1.executeQuery(User.class, sql, params.toArray());
        // 分页
        String countSql = "select count(*) from user where deleted = 0";
        long count = JdbcUtilsV1.executeQueryOneValue(countSql);
        PageInfo<User> pageInfo = new PageInfo<>(pageNo, pageSize, (int) count, users);
        return pageInfo;
    }
}
